﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using ColSqlHelper;
using System.Data.SqlClient;

namespace CoolChat
{
    public class Dbo
    {
        public static readonly DBTYPE dbtype = DBTYPE.MSACCESS;
        public static readonly string sqlconnectstring = SqlHelper.GetSqlConnection();
        public enum DBTYPE
        {
            MSSQL,
            MSACCESS,
            MYSQL,
            ORACLE
        }
        static Dbo()
        {
            string configtype = ConfigurationManager.AppSettings["dbtype"];
            switch (configtype.ToUpper())
            {
                case "MSSQL":
                    dbtype = DBTYPE.MSSQL;
                    break;
                case "MSACCESS":
                    dbtype = DBTYPE.MSACCESS;
                    break;
                //case "MYSQL":
                //    dbtype = DBTYPE.MYSQL;
                //    break;
                //case "ORACLE":
                //    dbtype = DBTYPE.ORACLE;
                //    break;
                default:
                    throw new Exception("unsupport dbtype");
            }
        }
        /// <summary>
        /// 使用UserID获取
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static ChatUser getChatUser(string userID)
        {
            DataTable dt = null;
            if (dbtype == DBTYPE.MSACCESS)
            {
                dt = AccessHelper.ExecuteTable(CommandType.Text,
                    "select * from [User] where userID=@userID",
                    new System.Data.OleDb.OleDbParameter("@userID", userID));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                dt = SqlHelper.ExecuteDataset(sqlconnectstring,CommandType.Text,
                    "select * from [User] where userID=@userID",
                    new SqlParameter("@userID", userID)).Tables[0];
            }
            if (dt.Rows.Count > 0)
            {
                return ChatUser.GetUser(dt.Rows[0]);
            }
            return null;
        }
        /// <summary>
        /// 使用记录的ID获取
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static ChatUser getChatUser(int id)
        {
            DataTable dt=null;
            if (dbtype == DBTYPE.MSACCESS)
            {
                dt = AccessHelper.ExecuteTable(CommandType.Text,
                    "select * from [User] where [id]=@id",
                    new System.Data.OleDb.OleDbParameter("@id", id));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                dt = SqlHelper.ExecuteDataset(sqlconnectstring, CommandType.Text,
                    "select * from [User] where [id]=@id",
                    new SqlParameter("@id", id)).Tables[0];
            }
            if (dt.Rows.Count > 0)
            {
                return ChatUser.GetUser(dt.Rows[0]);
            }
            return null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="uid">用户ID</param>
        /// <returns></returns>
        public static DateTime GetUserSilence(string uid)
        {
            DataTable dt = null;
            if (dbtype == DBTYPE.MSACCESS)
            {
                dt = AccessHelper.ExecuteTable(CommandType.Text,
                    "select [silenceExpires] from [User] where [userid]=@id",
                    new System.Data.OleDb.OleDbParameter("@id", uid));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                dt = SqlHelper.ExecuteDataset(sqlconnectstring, CommandType.Text,
                    "select [silenceExpires] from [User] where [userid]=@id",
                    new SqlParameter("@id", uid)).Tables[0];
            }
            if (dt.Rows.Count > 0)
            {
                return (DateTime)dt.Rows[0]["silenceExpires"];
            }
            return DateTime.Now;
        }

        public static int SetUserSilence(string uid,DateTime silence)
        {
            string sql = "update [User] set [silenceExpires]=@lt where [UserId]=@uid";
            if (dbtype == DBTYPE.MSACCESS)
            {
                return AccessHelper.ExecuteNonQuery(CommandType.Text, sql,
                        new System.Data.OleDb.OleDbParameter("@lt", silence.ToString("yyyy-MM-dd HH:mm:ss")),
                        new System.Data.OleDb.OleDbParameter("@uid", uid));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                return SqlHelper.ExecuteNonQuery(sqlconnectstring, CommandType.Text, sql,
                        new SqlParameter("@lt", silence.ToString("yyyy-MM-dd HH:mm:ss")),
                        new SqlParameter("@uid", uid));
            }
            throw new Exception("unsupport dbtype");
        }

        /// <summary>
        /// 不存在用户则加入
        /// </summary>
        public static int AddUserIfNotExists(ChatUser u)
        {
            string sql = "select [id] from [User] where userid=@uid";
            object o = null;
            if (dbtype == DBTYPE.MSACCESS)
            {
                o = AccessHelper.ExecuteScalar(CommandType.Text, sql, new System.Data.OleDb.OleDbParameter("@uid", u.UserID));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                o = SqlHelper.ExecuteScalar(sqlconnectstring, CommandType.Text, sql,
                    new SqlParameter("@uid", u.UserID));
            }
            if (o == null || o == DBNull.Value)
            {
                sql = "insert into [User]([UserID],[UserName],[UserIP],[SessionID],[UserRole],[LastLoginTime],[silenceExpires],[AddTime],[SysUserType],[Avatar])" +
                    "values(@UserID,@UserName,@UserIP,@SessionID,@UserRole,@LastLoginTime,@silenceExpires,@AddTime,@SysUserType,@Avatar)";

                if (dbtype == DBTYPE.MSACCESS)
                {
                    return AccessHelper.ExecuteNonQuery(CommandType.Text, sql,
                        new System.Data.OleDb.OleDbParameter("@UserID", u.UserID),
                        new System.Data.OleDb.OleDbParameter("@UserName", u.UserName),
                        new System.Data.OleDb.OleDbParameter("@UserIP", u.UserIP),
                        new System.Data.OleDb.OleDbParameter("@SessionID", u.SessionId),
                        new System.Data.OleDb.OleDbParameter("@UserRole", u.UserRole),
                        new System.Data.OleDb.OleDbParameter("@LastLoginTime", u.LastLoginTime.ToString("yyyy-MM-dd HH:mm:ss")),
                        new System.Data.OleDb.OleDbParameter("@silenceExpires", u.SilenceExpires.ToString("yyyy-MM-dd HH:mm:ss")),
                        new System.Data.OleDb.OleDbParameter("@AddTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                        new System.Data.OleDb.OleDbParameter("@SysUserType", u.SysUserType),
                        new System.Data.OleDb.OleDbParameter("@Avatar", u.Avatar));
                }
                else if (dbtype == DBTYPE.MSSQL)
                {
                    return SqlHelper.ExecuteNonQuery(sqlconnectstring,CommandType.Text, sql,
                        new SqlParameter("@UserID", u.UserID),
                        new SqlParameter("@UserName", u.UserName),
                        new SqlParameter("@UserIP", u.UserIP),
                        new SqlParameter("@SessionID", u.SessionId),
                        new SqlParameter("@UserRole", u.UserRole),
                        new SqlParameter("@LastLoginTime", u.LastLoginTime.ToString("yyyy-MM-dd HH:mm:ss")),
                        new SqlParameter("@silenceExpires", u.SilenceExpires.ToString("yyyy-MM-dd HH:mm:ss")),
                        new SqlParameter("@AddTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                        new SqlParameter("@SysUserType", u.SysUserType),
                        new SqlParameter("@Avatar", u.Avatar));
                }
            }
            return 0;
        }


        public static int AddMessage(ChatMessage cm)
        {

            string sql = @"insert into [Message]([FromID],[FromUserName],[ToID],[ToUserName],
[KeyWord],[MsgType],[Content],[AddTime],[FromUserIP],[ToUserIP],[MsgID],[MsgTime],
[FromUserType],[ToUserType]) values(@fromerID,@fromUserName,@toID,@toUserName,
@keyWord,@msgType,@content,@addtime,@fromUserIP,@toUserIP,@msgID,@msgTime,
@fromUserType,@toUserType)";
            int r = 0;
            if (dbtype == DBTYPE.MSACCESS)
            {
                r = AccessHelper.ExecuteNonQuery(System.Data.CommandType.Text, sql,
                    new System.Data.OleDb.OleDbParameter("@fromerID", cm.From.UserID),
                    new System.Data.OleDb.OleDbParameter("@fromUserName", cm.From.UserName),
                    new System.Data.OleDb.OleDbParameter("@toID", cm.To.UserID),
                    new System.Data.OleDb.OleDbParameter("@toUserName", cm.To.UserName),
                    new System.Data.OleDb.OleDbParameter("@keyWord", cm.Keyword),
                    new System.Data.OleDb.OleDbParameter("@msgType", (int)cm.Type),
                    new System.Data.OleDb.OleDbParameter("@content", cm.Content),
                    new System.Data.OleDb.OleDbParameter("@addtime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                    new System.Data.OleDb.OleDbParameter("@fromUserIP", cm.From.UserIP),
                    new System.Data.OleDb.OleDbParameter("@toUserIP", cm.To.UserIP),
                    new System.Data.OleDb.OleDbParameter("@msgID", cm.MsgID),
                    new System.Data.OleDb.OleDbParameter("@msgTime", cm.MsgTime.ToString("yyyy-MM-dd HH:mm:ss")),
                    new System.Data.OleDb.OleDbParameter("@fromUserType", cm.From.SysUserType),
                    new System.Data.OleDb.OleDbParameter("@toUserType", cm.To.SysUserType));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                r = SqlHelper.ExecuteNonQuery(sqlconnectstring,System.Data.CommandType.Text, sql,
                    new SqlParameter("@fromerID", cm.From.UserID),
                    new SqlParameter("@fromUserName", cm.From.UserName),
                    new SqlParameter("@toID", cm.To.UserID),
                    new SqlParameter("@toUserName", cm.To.UserName),
                    new SqlParameter("@keyWord", cm.Keyword),
                    new SqlParameter("@msgType", (int)cm.Type),
                    new SqlParameter("@content", cm.Content),
                    new SqlParameter("@addtime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                    new SqlParameter("@fromUserIP", cm.From.UserIP),
                    new SqlParameter("@toUserIP", cm.To.UserIP),
                    new SqlParameter("@msgID", cm.MsgID),
                    new SqlParameter("@msgTime", cm.MsgTime.ToString("yyyy-MM-dd HH:mm:ss")),
                    new SqlParameter("@fromUserType", cm.From.SysUserType),
                    new SqlParameter("@toUserType", cm.To.SysUserType));
            }
            return r;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="type">不指定类型传入 -1</param>
        /// <param name="bt">不指定时间传入 DateTime.MinValue</param>
        /// <param name="et">不指定时间传入 DateTime.MinValue</param>
        /// <param name="word">不指定传入空</param>
        /// <param name="username">不指定传入空</param>
        /// <param name="order">不指定传入 -1</param>
        /// <param name="count">不指定传入 -1</param>
        /// <returns></returns>
        public static List<ChatMessage> SelectMsgs(int type, DateTime bt, DateTime et, string word, string username, int order, int count)
        {

            if (dbtype == DBTYPE.MSACCESS)
            {
                #region MSACCESS
                List<System.Data.OleDb.OleDbParameter> ps = new List<System.Data.OleDb.OleDbParameter>();
                string sqlwhere = " where 1=1 ";
                if (type > -1)
                {
                    sqlwhere += " and [MsgType]=@type ";
                    ps.Add(new System.Data.OleDb.OleDbParameter("@type", type));
                }
                if (bt > DateTime.MinValue && et > DateTime.MinValue)
                {
                    sqlwhere += " and ([AddTime] between @bt and @et) ";
                    ps.Add(new System.Data.OleDb.OleDbParameter("@bt", bt));
                    ps.Add(new System.Data.OleDb.OleDbParameter("@et", et));
                }
                else if (bt > DateTime.MinValue)
                {
                    sqlwhere += " and [AddTime]>@bt ";
                    ps.Add(new System.Data.OleDb.OleDbParameter("@bt", bt));
                }
                else if (et > DateTime.MinValue)
                {
                    sqlwhere += " and [AddTime]<@bt ";
                    ps.Add(new System.Data.OleDb.OleDbParameter("@et", et));
                }
                if (!string.IsNullOrEmpty(word))
                {
                    sqlwhere += " and [KeyWord]=@k ";
                    ps.Add(new System.Data.OleDb.OleDbParameter("@k", word));
                }
                if (!string.IsNullOrEmpty(username))
                {
                    sqlwhere += " and [FromUserName]=@u ";
                    ps.Add(new System.Data.OleDb.OleDbParameter("@u", username));
                }
                if (order == 1)
                {
                    sqlwhere += " order by [AddTime],[ID] desc ";
                }
                else if (order == 0)
                {
                    sqlwhere += " order by [AddTime],[ID] asc ";
                }
                else
                {
                    sqlwhere += " order by [AddTime],[ID] desc ";
                }
                string sql = " select * from [Message] ";
                if (count > 0)
                {
                    sql = " select top " + count + " * from [Message] ";
                }
                DataTable dt = AccessHelper.ExecuteTable(System.Data.CommandType.Text, sql + sqlwhere,
                   ps.ToArray());
                ps.Clear();
                List<ChatMessage> msgs = new List<ChatMessage>();
                foreach (DataRow row in dt.Rows)
                {
                    msgs.Add(ChatMessage.GetMessage(row));
                }
                return msgs;
                #endregion
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                #region MSSQL
                List<SqlParameter> ps = new List<SqlParameter>();
                string sqlwhere = " where 1=1 ";
                if (type > -1)
                {
                    sqlwhere += " and [MsgType]=@type ";
                    ps.Add(new SqlParameter("@type", type));
                }
                if (bt > DateTime.MinValue && et > DateTime.MinValue)
                {
                    sqlwhere += " and ([AddTime] between @bt and @et) ";
                    ps.Add(new SqlParameter("@bt", bt));
                    ps.Add(new SqlParameter("@et", et));
                }
                else if (bt > DateTime.MinValue)
                {
                    sqlwhere += " and [AddTime]>@bt ";
                    ps.Add(new SqlParameter("@bt", bt));
                }
                else if (et > DateTime.MinValue)
                {
                    sqlwhere += " and [AddTime]<@bt ";
                    ps.Add(new SqlParameter("@et", et));
                }
                if (!string.IsNullOrEmpty(word))
                {
                    sqlwhere += " and [KeyWord]=@k ";
                    ps.Add(new SqlParameter("@k", word));
                }
                if (!string.IsNullOrEmpty(username))
                {
                    sqlwhere += " and [FromUserName]=@u ";
                    ps.Add(new SqlParameter("@u", username));
                }
                if (order == 1)
                {
                    sqlwhere += " order by [AddTime],[ID] desc ";
                }
                else if (order == 0)
                {
                    sqlwhere += " order by [AddTime],[ID] asc ";
                }
                else
                {
                    sqlwhere += " order by [AddTime],[ID] desc ";
                }
                string sql = " select * from [Message] ";
                if (count > 0)
                {
                    sql = " select top " + count + " * from [Message] ";
                }
                DataTable dt = SqlHelper.ExecuteDataset(sqlconnectstring,System.Data.CommandType.Text, sql + sqlwhere,
                   ps.ToArray()).Tables[0];
                ps.Clear();
                List<ChatMessage> msgs = new List<ChatMessage>();
                foreach (DataRow row in dt.Rows)
                {
                    msgs.Add(ChatMessage.GetMessage(row));
                }
                return msgs;
                #endregion
            }
            throw new Exception("unsupport dbtype");
        }

        /// <summary>
        /// mod: 0:等于;1:小于;2:大于;Default:大于;
        /// msgid msgtime msgaddtime 非必须
        /// </summary>
        /// <param name="msgid"></param>
        /// <param name="mod"></param>
        /// <param name="msgtime"></param>
        /// <param name="msgaddtime"></param>
        /// <returns></returns>
        public static List<ChatMessage> SelectMsgs(string mod, string msgid, string msgtime, string msgaddtime)
        {
            if (dbtype == DBTYPE.MSACCESS)
            {
                #region MSACCESS
                string op = ">";
                string sql_id = "";
                string sql_time = "";
                string sql_addtime = "";
                string sqlwhere = " where 1=1 ";
                List<System.Data.OleDb.OleDbParameter> ps = new List<System.Data.OleDb.OleDbParameter>();
                switch (mod)
                {
                    case "0":
                        op = "=";
                        break;
                    case "1":
                        op = "<";
                        break;
                    case "2":
                        op = ">";
                        break;
                    default:
                        op = ">";
                        break;
                }
                if (!string.IsNullOrEmpty(msgid))
                {
                    if (op == "=")
                    {
                        sql_id = " and [MsgID]=@msgID ";
                    }
                    else
                    {
                        sql_id = " and ID" + op + "(select top 1 [ID] from [Message] where [MsgID]=@msgID order by id asc) ";
                    }
                    ps.Add(new System.Data.OleDb.OleDbParameter("@msgID", msgid));
                }
                if (!string.IsNullOrEmpty(msgtime))
                {
                    sql_time = " and MsgTime" + op + "@msgTime ";
                    ps.Add(new System.Data.OleDb.OleDbParameter("@msgTime", DateTime.Parse(msgtime).ToString("yyyy-MM-dd HH:mm:ss")));
                }
                if (!string.IsNullOrEmpty(msgaddtime))
                {
                    sql_addtime = " and AddTime " + op + " @addtime ";
                    ps.Add(new System.Data.OleDb.OleDbParameter("@addtime", DateTime.Parse(msgaddtime).ToString("yyyy-MM-dd HH:mm:ss")));
                }
                if (sql_id == "" && sql_time == "" && sql_addtime == "")
                {
                    sqlwhere = " order by [ID] asc";
                }
                else
                {
                    sqlwhere += sql_id + sql_time + sql_addtime + " order by [ID] asc";
                }
                DataTable dt = AccessHelper.ExecuteTable(System.Data.CommandType.Text, "select * from [Message] " + sqlwhere,
                    ps.ToArray());
                ps.Clear();
                List<ChatMessage> msgs = new List<ChatMessage>();
                foreach (DataRow row in dt.Rows)
                {
                    msgs.Add(ChatMessage.GetMessage(row));
                }
                return msgs;
                #endregion
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                #region MSSQL
                string op = ">";
                string sql_id = "";
                string sql_time = "";
                string sql_addtime = "";
                string sqlwhere = " where 1=1 ";
                List<SqlParameter> ps = new List<SqlParameter>();
                switch (mod)
                {
                    case "0":
                        op = "=";
                        break;
                    case "1":
                        op = "<";
                        break;
                    case "2":
                        op = ">";
                        break;
                    default:
                        op = ">";
                        break;
                }
                if (!string.IsNullOrEmpty(msgid))
                {
                    if (op == "=")
                    {
                        sql_id = " and [MsgID]=@msgID ";
                    }
                    else
                    {
                        sql_id = " and ID" + op + "(select top 1 [ID] from [Message] where [MsgID]=@msgID order by id asc) ";
                    }
                    ps.Add(new SqlParameter("@msgID", msgid));
                }
                if (!string.IsNullOrEmpty(msgtime))
                {
                    sql_time = " and MsgTime" + op + "@msgTime ";
                    ps.Add(new SqlParameter("@msgTime", DateTime.Parse(msgtime).ToString("yyyy-MM-dd HH:mm:ss")));
                }
                if (!string.IsNullOrEmpty(msgaddtime))
                {
                    sql_addtime = " and AddTime " + op + " @addtime ";
                    ps.Add(new SqlParameter("@addtime", DateTime.Parse(msgaddtime).ToString("yyyy-MM-dd HH:mm:ss")));
                }
                if (sql_id == "" && sql_time == "" && sql_addtime == "")
                {
                    sqlwhere = " order by [ID] asc";
                }
                else
                {
                    sqlwhere += sql_id + sql_time + sql_addtime + " order by [ID] asc";
                }
                DataTable dt = SqlHelper.ExecuteDataset(sqlconnectstring,System.Data.CommandType.Text, "select * from [Message] " + sqlwhere,
                    ps.ToArray()).Tables[0];
                ps.Clear();
                List<ChatMessage> msgs = new List<ChatMessage>();
                foreach (DataRow row in dt.Rows)
                {
                    msgs.Add(ChatMessage.GetMessage(row));
                }
                return msgs;
                #endregion
            }
            throw new Exception("unsupport dbtype");
        }

        public static List<ChatMessage> SelectMsgs(DateTime start, DateTime end)
        {
            string sql = "select * from [Message] where [AddTime] between @s and @e";
            DataTable dt = null;
            if (dbtype == DBTYPE.MSACCESS)
            {
                dt = AccessHelper.ExecuteTable(CommandType.Text, sql,
                    new System.Data.OleDb.OleDbParameter("@s", start),
                    new System.Data.OleDb.OleDbParameter("@e", end));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                dt = SqlHelper.ExecuteDataset(sqlconnectstring,CommandType.Text, sql,
                    new SqlParameter("@s", start),
                    new SqlParameter("@e", end)).Tables[0];
            }
            List<ChatMessage> msgs = new List<ChatMessage>();
            foreach (DataRow row in dt.Rows)
            {
                msgs.Add(ChatMessage.GetMessage(row));
            }
            return msgs;
        }


        public static DataSet SelectMsgsDataSet(DateTime start, DateTime end)
        {
            string sql = "select * from [Message] where [AddTime] between @s and @e";
            DataTable dt = null;
            if (dbtype == DBTYPE.MSACCESS)
            {
                dt = AccessHelper.ExecuteTable(CommandType.Text, sql,
                    new System.Data.OleDb.OleDbParameter("@s", start),
                    new System.Data.OleDb.OleDbParameter("@e", end));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                dt = SqlHelper.ExecuteDataset(sqlconnectstring, CommandType.Text, sql,
                    new SqlParameter("@s", start),
                    new SqlParameter("@e", end)).Tables[0];
            }
            return dt.DataSet;
        }

        public static DataSet SelectExportMsgs(DateTime start, DateTime end)
        {
            string sql = "select [ID],[FromUserName],[FromUserIP],[ToUserName],[KeyWord],[Content],[AddTime] from [Message] where [MsgType]<>0 and ([AddTime] between @s and @e)";
            
            DataTable dt = null;
            if (dbtype == DBTYPE.MSACCESS)
            {
                dt = AccessHelper.ExecuteTable(CommandType.Text, sql,
                    new System.Data.OleDb.OleDbParameter("@s", start),
                    new System.Data.OleDb.OleDbParameter("@e", end));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                dt = SqlHelper.ExecuteDataset(sqlconnectstring,CommandType.Text, sql,
                    new SqlParameter("@s", start),
                    new SqlParameter("@e", end)).Tables[0];
            }
            return dt.DataSet;
        }

        public static List<ChatMessage> SelectTodayMsgs(string username)
        {
            string sql = "select * from [Message] where [MsgType]<>0 and ([AddTime] between @s and @e) and ([FromUserName]=@un or [ToUserName]=@un) and [ToUserName]<>'*' ";
            DateTime s = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0);
            DateTime et = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59);
            DataTable dt = null;
            if (dbtype == DBTYPE.MSACCESS)
            {
                dt = AccessHelper.ExecuteTable(CommandType.Text, sql,
                   new System.Data.OleDb.OleDbParameter("@s", s),
                   new System.Data.OleDb.OleDbParameter("@e", et),
                   new System.Data.OleDb.OleDbParameter("@un", username));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                dt = SqlHelper.ExecuteDataset(sqlconnectstring,CommandType.Text, sql,
                   new SqlParameter("@s", s),
                   new SqlParameter("@e", et),
                   new SqlParameter("@un", username)).Tables[0];
            }
            List<ChatMessage> msgs = new List<ChatMessage>();
            foreach (DataRow row in dt.Rows)
            {
                msgs.Add(ChatMessage.GetMessage(row));
            }
            return msgs;
        }

        public static ChatMessage GetLastMessage()
        {
            string sql = "select top 1 * from [Message] order by [id] desc";
            DataTable dt = null;
            if (dbtype == DBTYPE.MSACCESS)
            {
                dt = AccessHelper.ExecuteTable(CommandType.Text, sql);
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                dt = SqlHelper.ExecuteDataset(sqlconnectstring,CommandType.Text, sql).Tables[0];
            }
            ChatMessage cm = null;
            foreach (DataRow row in dt.Rows)
            {
                cm = ChatMessage.GetMessage(row);
            }
            return cm;
        }

        /// <summary>
        /// 判断是否被下线
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
        public static bool IsLogOff(ChatUser u)
        {
            string sql = "select [IsOnline] from [User] where [UserID]=@uid";
            object o = null;
            if (dbtype == DBTYPE.MSACCESS)
            {
                o = AccessHelper.ExecuteScalar(CommandType.Text, sql,
                    new System.Data.OleDb.OleDbParameter("@uid", u.UserID));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                o = SqlHelper.ExecuteScalar(sqlconnectstring, CommandType.Text, sql);
            }
            return !(bool)o;
        }

        public static int UpdateUserLoginState(ChatUser u)
        {
            
            //string sql = "update [User] set [LastLoginTime]=@lt where [UserId]=@uid";
            string sql = "update [User] set [LastLoginTime]=@lt, [IsOnline]=true where [UserId]=@uid";
            //if (u.UserRole == USER_ROLE.SYSUSER || u.UserRole == USER_ROLE.SA)
            //{//如果是系统用户和管理员用户,将更新在线状态为在线,以便在下线时不会重复判定已经下线
            //    sql = "update [User] set [LastLoginTime]=@lt, [IsOnline]=true where [UserId]=@uid";
            //}
            int r = -1;
            if (dbtype == DBTYPE.MSACCESS)
            {
                r = AccessHelper.ExecuteNonQuery(CommandType.Text, sql,
                        new System.Data.OleDb.OleDbParameter("@lt", u.LastLoginTime.ToString("yyyy-MM-dd HH:mm:ss")),
                        new System.Data.OleDb.OleDbParameter("@uid", u.UserID));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                sql = "update [User] set [LastLoginTime]=@lt, [IsOnline]=1 where [UserId]=@uid"; ;
                r = SqlHelper.ExecuteNonQuery(sqlconnectstring,CommandType.Text, sql,
                        new SqlParameter("@lt", u.LastLoginTime.ToString("yyyy-MM-dd HH:mm:ss")),
                        new SqlParameter("@uid", u.UserID));
            }
            return r;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="tick">毫秒,规定现在时间相差多少毫秒内算在线</param>
        /// <returns></returns>
        public static List<ChatUser> GetOlineUser(int tick)
        {
            DateTime loginTime = DateTime.Now.AddMilliseconds(-tick);
            string sql = "select [ID],[UserID],[UserName],[UserRole],[silenceExpires],[SysUserType],[IsOnline],[Avatar] from [User] where [LastLoginTime]>@loginTime order by [SysUserType],[UserName]";
            DataTable dt = null;
            if (dbtype == DBTYPE.MSACCESS)
            {
                dt = AccessHelper.ExecuteTable(CommandType.Text, sql,
                   new System.Data.OleDb.OleDbParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                dt = SqlHelper.ExecuteDataset(sqlconnectstring, CommandType.Text, sql,
                    new SqlParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss"))).Tables[0];
            }
            List<ChatUser> li = new List<ChatUser>();
            foreach (DataRow row in dt.Rows)
            {
                ChatUser u = ChatUser.GetUser(row);
                li.Add(u);
            }
            return li;
        }

        /// <summary>
        /// 获取不在线用户列表,并从数据库中删除他们(除了系统用户和超管用户)
        /// </summary>
        /// <param name="tick">毫秒,规定现在时间相差多少毫秒之前算下线</param>
        /// <returns></returns>
        public static List<ChatUser> GetOffLineUser(int tick)
        {
            if (dbtype == DBTYPE.MSACCESS)
            {
                #region MSACCESS
                DateTime loginTime = DateTime.Now.AddMilliseconds(-tick);
                //在状态为在线的用户中查找判定为下线的用户
                string sql = @"select [ID],[UserID],[UserName],[UserRole],[silenceExpires],[SysUserType] from [User] 
where [LastLoginTime]<@loginTime and [IsOnline]=true order by [SysUserType],[UserName]";
                DataTable dt = AccessHelper.ExecuteTable(CommandType.Text, sql,
                    new System.Data.OleDb.OleDbParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")));
                List<ChatUser> li = new List<ChatUser>();
                foreach (DataRow row in dt.Rows)
                {
                    ChatUser u = ChatUser.GetUser(row);
                    li.Add(u);
                }
                //将判定为下线的用户标记为下线
                sql = " update [User] set [IsOnline]=false where  [LastLoginTime]<@loginTime";// and [IsOnline]=true";
                AccessHelper.ExecuteNonQuery(CommandType.Text, sql,
                    new System.Data.OleDb.OleDbParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")));
                //删除用户除了系统用户和管理员用户
                sql = "delete from [User] where [LastLoginTime]<@loginTime and ([UserRole]<>3 and [UserRole]<>2 )";
                AccessHelper.ExecuteNonQuery(CommandType.Text, sql,
                    new System.Data.OleDb.OleDbParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")));
                return li;
                #endregion
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                #region MSSQL
                DateTime loginTime = DateTime.Now.AddMilliseconds(-tick);
                //在状态为在线的用户中查找判定为下线的用户
                string sql = @"select [ID],[UserID],[UserName],[UserRole],[silenceExpires],[SysUserType] from [User] 
where [LastLoginTime]<@loginTime and [IsOnline]=1 order by [SysUserType],[UserName]";
                DataTable dt = SqlHelper.ExecuteDataset(sqlconnectstring,CommandType.Text, sql,
                    new SqlParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss"))).Tables[0];
                List<ChatUser> li = new List<ChatUser>();
                foreach (DataRow row in dt.Rows)
                {
                    ChatUser u = ChatUser.GetUser(row);
                    li.Add(u);
                }
                //将判定为下线的用户标记为下线
                sql = " update [User] set [IsOnline]=0 where  [LastLoginTime]<@loginTime";// and [IsOnline]=true";
                SqlHelper.ExecuteNonQuery(sqlconnectstring,CommandType.Text, sql,
                    new SqlParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")));
                //删除用户除了系统用户和管理员用户
                sql = "delete from [User] where [LastLoginTime]<@loginTime and ([UserRole]<>3 and [UserRole]<>2 )";
                SqlHelper.ExecuteNonQuery(sqlconnectstring, CommandType.Text, sql,
                    new SqlParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")));
                return li;
                #endregion
            }
            throw new Exception("unsupport dbtype");
        }

        /// <summary>
        /// 对单一的用户进行离线判断操作
        /// 获取不在线用户列表,并从数据库中删除他们(除了系统用户和超管用户)
        /// </summary>
        /// <param name="tick">毫秒,规定现在时间相差多少毫秒之前算下线</param>
        /// <returns>true 用户已被下线</returns>
        public static bool GetOffLineUser(int tick,ChatUser u)
        {
            if (dbtype == DBTYPE.MSACCESS)
            {
                #region MSACCESS
                DateTime loginTime = DateTime.Now.AddMilliseconds(-tick);
                //在状态为在线的用户中查找判定为下线的用户
                string sql = @"select [ID],[UserID],[UserName],[UserRole],[silenceExpires],[SysUserType] from [User] 
where [LastLoginTime]<@loginTime and [IsOnline]=true and [UserID]=@uid order by [SysUserType],[UserName]";
                DataTable dt = AccessHelper.ExecuteTable(CommandType.Text, sql,
                    new System.Data.OleDb.OleDbParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")),
                    new System.Data.OleDb.OleDbParameter("@uid", u.UserID));
                if (dt.Rows.Count > 0)
                {
                    //将判定为下线的用户标记为下线
                    sql = " update [User] set [IsOnline]=false where  [LastLoginTime]<@loginTime and [UserID]=@uid";// and [IsOnline]=true";
                    int r = AccessHelper.ExecuteNonQuery(CommandType.Text, sql,
                        new System.Data.OleDb.OleDbParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")),
                        new System.Data.OleDb.OleDbParameter("@uid", u.UserID));
                    //删除用户除了系统用户和管理员用户
                    sql = "delete from [User] where [LastLoginTime]<@loginTime and ([UserRole]<>3 and [UserRole]<>2 ) and [UserID]=@uid";
                    r = AccessHelper.ExecuteNonQuery(CommandType.Text, sql,
                        new System.Data.OleDb.OleDbParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")),
                        new System.Data.OleDb.OleDbParameter("@uid", u.UserID));
                    return true;
                }
                return false;
                #endregion
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                #region MSSQL
                DateTime loginTime = DateTime.Now.AddMilliseconds(-tick);
                //在状态为在线的用户中查找判定为下线的用户
                string sql = @"select [ID],[UserID],[UserName],[UserRole],[silenceExpires],[SysUserType] from [User] 
where [LastLoginTime]<@loginTime and [IsOnline]=1 and [UserID]=@uid order by [SysUserType],[UserName]";
                DataTable dt = SqlHelper.ExecuteDataset(sqlconnectstring,CommandType.Text, sql,
                    new SqlParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")),
                    new SqlParameter("@uid", u.UserID)).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    //将判定为下线的用户标记为下线
                    sql = " update [User] set [IsOnline]=0 where  [LastLoginTime]<@loginTime and [UserID]=@uid";// and [IsOnline]=true";
                    int r = SqlHelper.ExecuteNonQuery(sqlconnectstring, CommandType.Text, sql,
                        new SqlParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")),
                        new SqlParameter("@uid", u.UserID));
                    //删除用户除了系统用户和管理员用户
                    sql = "delete from [User] where [LastLoginTime]<@loginTime and ([UserRole]<>3 and [UserRole]<>2 ) and [UserID]=@uid";
                    r = SqlHelper.ExecuteNonQuery(sqlconnectstring, CommandType.Text, sql,
                        new SqlParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")),
                        new SqlParameter("@uid", u.UserID));
                    return true;
                }
                return false;
                #endregion
            }
            throw new Exception("unsupport dbtype");
        }

        /// <summary>
        /// 如果用户判定为不在线返回TRUE
        /// </summary>
        /// <param name="tick"></param>
        /// <param name="u"></param>
        /// <returns></returns>
        public static bool CheckUserOffLineByTime(int tick,ChatUser u)
        {
            if (dbtype == DBTYPE.MSACCESS)
            {
                DateTime loginTime = DateTime.Now.AddMilliseconds(-tick);
                //用户存在数据库中 判断时间
                string sql = "select 1 from [User] where [LastLoginTime]<@loginTime and [UserID]=@uid";
                object o = AccessHelper.ExecuteScalar(CommandType.Text, sql,
                    new System.Data.OleDb.OleDbParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")),
                    new System.Data.OleDb.OleDbParameter("@uid", u.UserID));
                return o == null || o == DBNull.Value ? false : true;
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                DateTime loginTime = DateTime.Now.AddMilliseconds(-tick);
                //用户存在数据库中 判断时间
                string sql = "select 1 from [User] where [LastLoginTime]<@loginTime and [UserID]=@uid";
                object o = SqlHelper.ExecuteScalar(sqlconnectstring,CommandType.Text, sql,
                    new SqlParameter("@loginTime", loginTime.ToString("yyyy-MM-dd HH:mm:ss")),
                    new SqlParameter("@uid", u.UserID));
                return o == null || o == DBNull.Value ? false : true;
            }
            throw new Exception("unsupport dbtype");
        }

        public static void AddOnLineMsg(ChatUser u)
        {
            ChatMessage loginmessage = new ChatMessage();
            loginmessage.From = new ChatUser();
            loginmessage.To = u;
            //loginmessage.To.UserIP = Request.UserHostAddress;
            loginmessage.From.UserName = "sysmessage";
            loginmessage.From.UserID = "system";
            loginmessage.From.UserIP = "*";
            loginmessage.Type = MsgType.SYSMSG;
            loginmessage.Content = "jionchat";
            loginmessage.MsgID = Tools.md5(u.UserName + u.UserIP + DateTime.Now.Ticks + u.SessionId, 32);
            loginmessage.MsgTime = DateTime.Now;
            loginmessage.Addtime = DateTime.Now;
            Dbo.AddMessage(loginmessage);
        }
        public static void AddOffLineMsg(ChatUser u)
        {
            ChatMessage loginmessage = new ChatMessage();
            loginmessage.From = new ChatUser();
            loginmessage.To = u;
            //loginmessage.To.UserIP = Request.UserHostAddress;
            loginmessage.From.UserName = "sysmessage";
            loginmessage.From.UserID = "system";
            loginmessage.From.UserIP = "*";
            loginmessage.Type = MsgType.SYSMSG;
            loginmessage.Content = "outchat";
            loginmessage.MsgID = Tools.md5(u.UserName + u.UserIP + DateTime.Now.Ticks + u.SessionId, 32);
            loginmessage.MsgTime = DateTime.Now;
            loginmessage.Addtime = DateTime.Now;
            Dbo.AddMessage(loginmessage);
        }
        public static int DeleteMsgBeforeAddTime(DateTime time)
        {
            if (dbtype == DBTYPE.MSACCESS)
            {
                string sql = "delete from [Message] where [AddTime]<@time";
                return AccessHelper.ExecuteNonQuery(CommandType.Text, sql,
                    new System.Data.OleDb.OleDbParameter("@time", time));
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                string sql = "delete from [Message] where [AddTime]<@time";
                return SqlHelper.ExecuteNonQuery(sqlconnectstring,CommandType.Text, sql,
                    new SqlParameter("@time", time));
            }
            throw new Exception("unsupport dbtype");
        }
        public static string GetTypeName(int typeid)
        {
            if (dbtype == DBTYPE.MSACCESS)
            {
                string sql = "select TypeName from SysUserType where typeID=@id";
                DataTable dt = AccessHelper.ExecuteTable(System.Data.CommandType.Text,
                    sql, new System.Data.OleDb.OleDbParameter("@id", typeid));
                return dt.Rows[0]["TypeName"] == null ? null : dt.Rows[0]["TypeName"].ToString();
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                string sql = "select TypeName from SysUserType where typeID=@id";
                DataTable dt = SqlHelper.ExecuteDataset(sqlconnectstring,System.Data.CommandType.Text,
                    sql, new SqlParameter("@id", typeid)).Tables[0];
                return dt.Rows[0]["TypeName"] == null ? null : dt.Rows[0]["TypeName"].ToString();
            }
            throw new Exception("unsupport dbtype");
        }
        public static string GetSysUserTypeNameJson(string lancode)
        {
            if (dbtype == DBTYPE.MSACCESS)
            {
                string sql = "select * from SysUserType where LANCODE=@lan";
                DataTable dt = AccessHelper.ExecuteTable(System.Data.CommandType.Text,
                    sql, new System.Data.OleDb.OleDbParameter("@lan", lancode));
                string json = "{";
                foreach (DataRow r in dt.Rows)
                {
                    json += "\"" + r["typeID"] + "\":\"" + r["TypeName"] + "\",";
                }
                json += "\"length\":" + (dt.Rows.Count - 1);
                //json = json.Substring(0, json.Length - 1);
                json += "}";
                return json;
            }
            else if (dbtype == DBTYPE.MSSQL)
            {
                string sql = "select * from SysUserType where LANCODE=@lan";
                DataTable dt = SqlHelper.ExecuteDataset(sqlconnectstring,System.Data.CommandType.Text,
                    sql, new SqlParameter("@lan", lancode)).Tables[0];
                string json = "{";
                foreach (DataRow r in dt.Rows)
                {
                    json += "\"" + r["typeID"] + "\":\"" + r["TypeName"] + "\",";
                }
                json += "\"length\":" + (dt.Rows.Count - 1);
                //json = json.Substring(0, json.Length - 1);
                json += "}";
                return json;
            }
            throw new Exception("unsupport dbtype");
        }
    }
}